﻿/*== TSL_prc_PerfmonCounterCounts ========================================================================
* Returns the quantity of counters and instances collected in a given test run.
* EXAMPLE:
EXEC	TSL_prc_PerfmonCounterCounts
		@RunId = N'9'
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_PerfmonCounterCounts
		@RunId int
	AS
	BEGIN
		SELECT a.LoadTestRunId, COUNT(DISTINCT b.CounterId) AS NumCounters, COUNT(a.InstanceId) AS numTotalInstances
		FROM dbo.[LoadTestPerformanceCounterInstance] AS a
		INNER JOIN dbo.LoadTestPerformanceCounter AS b
		ON b.LoadTestRunId = a.LoadTestRunId
		AND b.CounterId = a.CounterId
		WHERE a.LoadTestRunId = @RunId
		GROUP BY a.LoadTestRunId
		ORDER BY a.LoadTestRunId ASC
	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_PerfmonCounterCounts TO PUBLIC
	GO

/*== TSL_prc_PerfCounterExportToCSV ============================================================
* Returns a list of perfmon counter names and values in a format that can be written
* to a CSV file for import into Perfmon.
* EXAMPLE:
EXEC	TSL_prc_PerfCounterExportToCSV
		@RunId = N'9',
		@InstanceName = N'\\controller\%'
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_PerfCounterExportToCSV
		@RunId nvarchar(10),
		@InstanceName nvarchar(1024)
	AS

		DECLARE @CounterName nvarchar(max), @CounterNameColumns nvarchar(max)

		--Get List of columns to use in query. Shove them into a single long XML string
		SELECT @CounterNameColumns = (
						SELECT ', [' + REPLACE(InstanceName, ']', ']]') + ']' FROM MTSL_View_PerfmonInstanceNamesAndIds
						WHERE LoadTestRunId = @RunId
						AND InstanceName LIKE @InstanceName
				FOR XML PATH(''))


		--Make a copy of the list WITHOUT the comma at the very beginning of the string
		SELECT @CounterName = RIGHT(@CounterNameColumns, LEN(@CounterNameColumns) - 1)


		-- Use the previous strings to build the query string that can be pivoted
		DECLARE @SQL nvarchar(max)
		 SELECT @SQL = N'
			select
			IntervalStartTime AS [(PDH-CSV 4.0) (Eastern Daylight Time)(240)]' + 
		   --IntervalStartTime' + 
			@CounterNameColumns + '
		 from (
				select
					interval.IntervalStartTime,
					MTSL_View_PerfmonInstanceNamesAndIds.InstanceName,
					countersample.ComputedValue
				FROM 
					MTSL_View_PerfmonInstanceNamesAndIds

				INNER JOIN LoadTestPerformanceCounterSample AS countersample 
					ON countersample.InstanceId = MTSL_View_PerfmonInstanceNamesAndIds.InstanceId
					AND countersample.LoadTestRunId = MTSL_View_PerfmonInstanceNamesAndIds.LoadTestRunId

				INNER JOIN LoadTestRunInterval AS interval 
					ON interval.LoadTestRunId = countersample.LoadTestRunId
					AND interval.TestRunIntervalId = countersample.TestRunIntervalId

				WHERE
					MTSL_View_PerfmonInstanceNamesAndIds.LoadTestRunId = ' + @RunId + '
					AND 
					MTSL_View_PerfmonInstanceNamesAndIds.InstanceName LIKE '''+@InstanceName+'''
		 ) Data
		 PIVOT (
		   SUM(ComputedValue)
		   FOR InstanceName
		   IN (
			 ' + @CounterName + '
		   )
		 ) PivotTable
		 ORDER BY IntervalStartTime ASC
		 '

		 print @SQL
		-- Execute the generated query
		exec sp_executesql @SQL
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_PerfCounterExportToCSV TO PUBLIC
	GO

/*== TSL_prc_GetStartingValuesForImportingPerfmon ========================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_GetStartingValuesForImportingPerfmon
		@RunId  int
	AS
	BEGIN
		DECLARE @tt TABLE(
			FirstIntervalTime DATETIME
			,LastIntervalTime DATETIME
			,CounterCategoryId INT
			,CounterId INT
			,InstanceId INT
			,IntervalSpan INT
			,IntervalQty INT
			)

		INSERT INTO @tt (FirstIntervalTime) 
			SELECT TOP 1 IntervalStartTime FROM LoadTestRunInterval
			WHERE LoadTestRunId = @RunId AND IntervalStartTime != IntervalEndTime
			ORDER BY IntervalStartTime ASC

		UPDATE @tt SET LastIntervalTime = (SELECT TOP 1 IntervalStartTime FROM LoadTestRunInterval
			WHERE LoadTestRunId = @RunId AND IntervalStartTime != IntervalEndTime
			ORDER BY IntervalStartTime DESC)

		UPDATE @tt SET CounterCategoryId = (SELECT TOP 1 CounterCategoryId FROM LoadTestPerformanceCounterCategory 
			WHERE LoadTestRunId = @RunId ORDER BY CounterCategoryId DESC) + 1

		UPDATE @tt SET CounterId = (SELECT TOP 1 CounterId FROM LoadTestPerformanceCounter 
			WHERE LoadTestRunId = @RunId ORDER BY CounterId DESC) + 1

		UPDATE @tt SET InstanceId = (SELECT TOP 1 InstanceId FROM LoadTestPerformanceCounterInstance 
			WHERE LoadTestRunId = @RunId ORDER BY InstanceId DESC) + 1

		UPDATE @tt SET IntervalQty = (SELECT COUNT(TestRunIntervalId) FROM LoadTestRunInterval 
			WHERE LoadTestRunId = @RunId )

		UPDATE @tt SET IntervalSpan = (SELECT DATEDIFF(second,IntervalStartTime ,IntervalEndTime) FROM LoadTestRunInterval 
			WHERE TestRunIntervalId = 1 AND LoadTestRunId = @RunId)

		SELECT * FROM @tt
	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_GetStartingValuesForImportingPerfmon TO PUBLIC
	GO


/*== TSL_prc_UpdatePerfmonTablesFromImport ========================================================
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE TSL_prc_UpdatePerfmonTablesFromImport
		@RunId  int
	AS
	BEGIN
		-- Update the CounterCategory Table -----------------------------------------------------------
		 DECLARE @catId INT
		 SET @catId = (SELECT TOP 1 CounterCategoryId FROM LoadTestPerformanceCounterCategory WHERE LoadTestRunId = @RunId ORDER BY CounterCategoryId DESC)
		 DECLARE @TimeStamp BIGINT = 0
 
		 INSERT INTO LoadTestPerformanceCounterCategory([LoadTestRunId],[CounterCategoryId],[CategoryName],[MachineName],[StartTimeStamp100nSec])
			SELECT @RunId, @catId + ROW_NUMBER() OVER (ORDER BY @catId) AS [identity]
				, categoryName, machineName, @TimeStamp FROM TSL_StagingTableForPerfmonImports GROUP BY machineName, categoryName


		-- Update the Counter Table -------------------------------------------------------------------
		DECLARE @counterId INT
		 SET @counterId = (SELECT TOP 1 CounterId FROM LoadTestPerformanceCounter WHERE LoadTestRunId = @RunId ORDER BY CounterId DESC)

		INSERT INTO LoadTestPerformanceCounter (LoadTestRunId, CounterCategoryId, CounterId, CounterName, HigherIsBetter)
			SELECT @RunId, b.CounterCategoryId, @counterId + ROW_NUMBER() OVER (ORDER BY @counterId) AS [identity]
				,a.counterName, NULL FROM TSL_StagingTableForPerfmonImports AS a
			INNER JOIN LoadTestPerformanceCounterCategory AS b
				ON b.LoadTestRunId = @RunId 
				AND a.machineName = b.MachineName 
				AND b.CategoryName = a.categoryName
			GROUP BY a.counterName, b.CounterCategoryId



		-- Update the Instance Table ------------------------------------------------------------------
		INSERT INTO LoadTestPerformanceCounterInstance (LoadTestRunId, CounterId, InstanceId, LoadTestItemId, InstanceName, CumulativeValue, OverallThresholdRuleResult)
			SELECT @RunId, b.CounterId, a.InstanceId, NULL, a.instanceName, a.cumulativeValue, NULL
			FROM TSL_StagingTableForPerfmonImports AS a
			INNER JOIN LoadTestPerformanceCounter AS b 
				ON b.LoadTestRunId = @RunId
				AND a.counterName = b.CounterName
			INNER JOIN LoadTestPerformanceCounterCategory AS c
				ON c.LoadTestRunId = @RunId
				AND a.machineName = c.MachineName 
				AND a.categoryName = c.CategoryName
			WHERE b.CounterCategoryId = c.CounterCategoryId

		EXEC TSL_prc_UpdateScoreCardMachineCpuTable

		EXEC TSL_prc_UpdateScoreCardMachineAvailableMBTable
	END
	GO
	--START CODE--
	GRANT EXECUTE ON TSL_prc_UpdatePerfmonTablesFromImport TO PUBLIC
	GO

